﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBSchemaReader
{
	internal class SchemaReader : ISchemaReader
	{
		private SqlConnectionStringBuilder _conn;

		private string _selectTableInfoQuery = " SELECT [TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME] FROM [INFORMATION_SCHEMA].[TABLES] ";
		private string _selectColumnByTable = " SELECT [COLUMN_NAME],[DATA_TYPE],[CHARACTER_MAXIMUM_LENGTH] FROM [INFORMATION_SCHEMA].[COLUMNS] " +
					" WHERE [TABLE_SCHEMA] = @SCHEMA AND [TABLE_NAME] = @TABLE; ";

		internal SchemaReader()
		{
			_conn = new SqlConnectionStringBuilder();
			_conn.DataSource = "";
			_conn.IntegratedSecurity = true;
			_conn.InitialCatalog = "";
		}

		public string DataSource
		{
			get
			{
				return _conn.DataSource;
			}
			set
			{
				_conn.DataSource = value;
			}
		}

		public string DatabaseName
		{
			get
			{
				return _conn.InitialCatalog;
			}
			set
			{
				_conn.InitialCatalog = value;
			}
		}

		public List<TableInfo> GetTablesInfo()
		{
			using (SqlConnection conn = new SqlConnection(_conn.ConnectionString))
			using (var command = new SqlCommand(_selectTableInfoQuery, conn))
			{
				List<TableInfo> tables = new List<TableInfo>();

				conn.Open();
				using (SqlDataReader dr = command.ExecuteReader())
				{
					while (dr.Read())
					{
						var dto = LoadTableInfo(dr);
						tables.Add(dto);
					}
				}
				return tables;
			}
		}

		public List<ColumnInfo> GetColumnsInfo(TableInfo table)
		{
			using (SqlConnection conn = new SqlConnection(_conn.ConnectionString))
			using (var command = new SqlCommand(_selectColumnByTable, conn))
			{
				List<ColumnInfo> columns = new List<ColumnInfo>();

				command.Parameters.AddWithValue("@SCHEMA", table.SchemaName);
				command.Parameters.AddWithValue("@TABLE", table.TableName);

				conn.Open();

				using (SqlDataReader dr = command.ExecuteReader())
				{
					while (dr.Read())
					{
						ColumnInfo dto = LoadColumnInfo(dr);
						columns.Add(dto);
					}
				}

				return columns;
			}
		}

		private TableInfo LoadTableInfo(SqlDataReader dr)
		{
			string catalog = dr.GetString("TABLE_CATALOG");
			string schema = dr.GetString("TABLE_SCHEMA");
			string table = dr.GetString("TABLE_NAME");

			return new TableInfo(catalog, schema, table);
		}

		private ColumnInfo LoadColumnInfo(SqlDataReader dr)
		{
			string columnName = dr.GetString("COLUMN_NAME");
			string type = dr.GetString("DATA_TYPE");
			int? length = dr.GetInt32Nullable("CHARACTER_MAXIMUM_LENGTH");
			if (length > 0)
			{
				type += "(" + length + ")";
			}
			return new ColumnInfo(columnName, type);
		}
	}
}
